﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LA.Extensions;
using System.Data;
using Npgsql;

namespace Drive.DbAccess
{
    class PostgreDb : IDbInterface
    {
        private JdbcString jdbcString;

        public string Name { get; private set; }

        public PostgreDb(JdbcString jdbcString, string name)
        {
            this.jdbcString = jdbcString;
            this.Name = name;
        }

        public void TestConnection()
        {
            using (var connection = this.NewConnection() as NpgsqlConnection)
            {
                connection.ConnectionString = GetConnectionString();
                connection.Open();
            }
        }

        private DbConnection NewConnection()
        {
            return new NpgsqlConnection();
        }

        private string GetConnectionString()
        {
            var csb = new NpgsqlConnectionStringBuilder();
            csb.Host = jdbcString.Host;
            csb.Port = jdbcString.Port;
            csb.Database = jdbcString.Database;
            csb.Username = jdbcString.UserName;
            csb.Password = jdbcString.Password;
            return csb.ConnectionString;
        }

        public IEnumerable<string> GetDatabases()
        {
            var cmd = this.NewCommand();
            cmd.CommandText = "select database();";
            using (var conn = this.NewConnection())
            {
                conn.ConnectionString = this.GetConnectionString();
                Drive.DbAccess.DTable dTable = conn.ExecuteDTable("select database();");
                return dTable.Rows.Select(x => x.GetString(0));
            }
        }

        public DbCommand NewCommand()
        {
            return new NpgsqlCommand();
        }


        public IEnumerable<string> GetTables()
        {
            var sql = $"select * from information_schema.TABLES where TABLE_SCHEMA = '{this.jdbcString.Database}'";
            using (var conn = this.NewConnection())
            {
                conn.ConnectionString = this.GetConnectionString();
                Drive.DbAccess.DTable dTable = conn.ExecuteDTable(sql);
                return dTable.Rows.Select(x => x.GetString("TABLE_NAME"));
            }
        }

        public IEnumerable<DataColumn> GetColumns(string tableName)
        {
            var sql = $"select * from information_schema.COLUMNS where TABLE_SCHEMA = '{this.jdbcString.Database}' AND TABLE_NAME='{tableName}'";
            using (var conn = this.NewConnection())
            {
                conn.ConnectionString = this.GetConnectionString();
                Drive.DbAccess.DTable dTable = conn.ExecuteDTable(sql);
                return dTable.Rows.Select(x => new DataColumn
                {
                    ColumnName = x.GetString("COLUMN_NAME"),
                    DataType = GetDataType(x.GetString("DATA_TYPE"))
                });
            }
        }
        public Type GetDataType(string type)
        {
            switch (type)
            {
                case "int":
                    return typeof(int);
                case "bigint":
                    return typeof(long);
                case "varchar":
                    return typeof(string);
                case "datetime":
                    return typeof(DateTime);
                default:
                    throw new NotImplementedException();
                    break;
            }
            return null;
        }

        public int Update(DataTable datatable, string tableName, List<ColumnMapping> columnMappings, string updateByColumn)
        {
            var dt = new DataTable() { TableName = tableName };
            int updateByIndex = -1;
            if (!string.IsNullOrEmpty(updateByColumn))
            {
                var updateless = new List<object>();
                var indies = new List<int>();
                var idx = 0;
                foreach (DataColumn item in datatable.Columns)
                {
                    var col = columnMappings.Find(x => x.Enabled && x.Src == item.ColumnName);
                    if (col != null)
                    {
                        if (col.Dst.Equals(updateByColumn, StringComparison.OrdinalIgnoreCase))
                        {
                            updateByIndex = idx;
                        }
                        else
                        {
                            col.SrcIndex = idx;
                            indies.Add(idx);
                        }
                    }
                    idx++;
                }
                if (updateByIndex == -1)
                    throw new Exception("updateByIndex invalid.");

                var maps = columnMappings.Where(x => x.SrcIndex != -1);
                var sql = $"UPDATE {tableName} " +
                    $"SET {string.Join(",", maps.Select((x, i) => $"{x.Dst}=@p{i}"))} " +
                    $"WHERE {updateByColumn}=@by";

                using (var conn = this.NewConnection())
                {
                    conn.ConnectionString = this.GetConnectionString();
                    conn.Open();
                    using (var trans = conn.BeginTransaction())
                    {
                        try
                        {
                            int rowsAffected = 0;
                            foreach (DataRow item in datatable.Rows)
                            {
                                var items = item.ItemArray.Where((x, i) => indies.Contains(i));
                                var command = this.NewCommand();
                                command.CommandText = sql;
                                command.Connection = conn;
                                for (int i = 0; i < items.Count(); i++)
                                {
                                    var value = items.ElementAt(i);
                                    if (columnMappings[i].Sql != null)
                                    {
                                        value = ExecuteSqlForParameter(columnMappings[i].Sql, value);
                                        if (value == DBNull.Value)
                                            value = null;
                                    }
                                    command.Parameters.Add(new NpgsqlParameter($"@p{i}", value));
                                }
                                command.Parameters.Add(new NpgsqlParameter("@by", item.ItemArray[updateByIndex]));
                                var n = command.ExecuteNonQuery();
                                if (n == 0)
                                {
                                    if (updateless == null)
                                        updateless = new List<object>();
                                    updateless.Add(item.ItemArray[updateByIndex]);
                                }
                                rowsAffected += n;
                            }
                            trans.Commit();
                            return rowsAffected;
                        }
                        catch
                        {
                            trans.Rollback();
                            throw;
                        }
                    }
                }
            }
            else
            {
                return this.Insert(datatable, tableName, columnMappings);
            }
            return 0;
        }

        private int Insert(DataTable datatable, string tableName, List<ColumnMapping> columnMappings)
        {
            var maps = columnMappings.Where(x => x.Enabled);
            var sql = $"INSERT INTO {tableName} ({string.Join(",", maps.Select(x => x.Dst))})" +
                $"VALUES({string.Join(",", maps.Select((x, i) => $"@p{i}"))})";

            var autoInc = columnMappings.Where(x => x.Enabled && x.FixedValueAI).ToDictionary(x => x.Dst, x => int.Parse(x.FixedValue));
            using (var conn = this.NewConnection())
            {
                conn.ConnectionString = this.GetConnectionString();
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        int rowsAffected = 0;

                        foreach (DataRow item in datatable.Rows)
                        {
                            var command = this.NewCommand();
                            command.CommandText = sql;
                            command.Connection = conn;
                            var i = 0;
                            foreach (var map in columnMappings.Where(x => x.Enabled))
                            {
                                if (map.Sql == null)
                                {
                                    if (map.UseFixedValue)
                                    {
                                        if (map.FixedValueAI)
                                        {
                                            command.Parameters.Add(new NpgsqlParameter($"@p{i}", autoInc[map.Dst]));
                                            autoInc[map.Dst]++;
                                        }
                                        else
                                        {
                                            command.Parameters.Add(new NpgsqlParameter($"@p{i}", map.FixedValue));
                                        }
                                    }
                                    else
                                    {
                                        var col = datatable.Columns.OfType<DataColumn>().FirstOrDefault(x => x.ColumnName == map.Src);
                                        if (col == null)
                                        {
                                            throw new Exception($"src column {map.Src} invalid.");
                                        }
                                        var columnIdx = datatable.Columns.IndexOf(col);
                                        var value = item.ItemArray[columnIdx];
                                        if (value == DBNull.Value)
                                            value = null;
                                        if (value == null && map.ReplaceNull)
                                            value = "";
                                        command.Parameters.Add(new NpgsqlParameter($"@p{i}", value));
                                    }
                                }
                                else
                                {
                                    object value = null;
                                    if (map.Sql.IndexOf("@p") != -1)
                                    {
                                        var col = datatable.Columns.OfType<DataColumn>().FirstOrDefault(x => x.ColumnName == map.Src);
                                        if (col == null)
                                        {
                                            throw new Exception($"src column {map.Src} invalid.");
                                        }
                                        var columnIdx = datatable.Columns.IndexOf(col);
                                        value = item.ItemArray[columnIdx];
                                        value = ExecuteSqlForParameter(map.Sql, value);
                                    }
                                    else
                                    {
                                        value = ExecuteSqlForParameter(map.Sql);
                                    }
                                    if (value == DBNull.Value)
                                        value = null;
                                    command.Parameters.Add(new NpgsqlParameter($"@p{i}", value));
                                }
                                i++;
                            }
                            try
                            {
                                var n = command.ExecuteNonQuery();
                                rowsAffected += n;
                            }
                            catch (Exception ex)
                            {
                                throw;
                            }
                        }
                        trans.Commit();
                        return rowsAffected;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        private object ExecuteSqlForParameter(string sql, object p)
        {
            using (var conn = this.NewConnection())
            {
                conn.ConnectionString = this.GetConnectionString();
                var cmd = this.NewCommand();
                cmd.Parameters.Add(new NpgsqlParameter("@p", p));
                cmd.CommandText = sql;
                cmd.Connection = conn;
                conn.Open();
                return cmd.ExecuteScalar();
            }
        }

        private object ExecuteSqlForParameter(string sql)
        {
            using (var conn = this.NewConnection())
            {
                conn.ConnectionString = this.GetConnectionString();
                var cmd = this.NewCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                conn.Open();
                return cmd.ExecuteScalar();
            }
        }

        public DataTable ExecuteDataTable(string sql)
        {
            using (var conn = this.NewConnection())
            {
                var dt = new DataTable();
                conn.ConnectionString = this.GetConnectionString();
                var cmd = this.NewCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                conn.Open();
                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd as NpgsqlCommand);
                adapter.Fill(dt);
                return dt;
            }
        }
    }
}
